/**
 * openfire_src
 */
package com.kingray.openfire.plugin.dao.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;

import org.jivesoftware.database.DbConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kingray.openfire.plugin.dao.EmotionDAO;
import com.kingray.openfire.plugin.vo.Emotion;
import com.kingray.openfire.plugin.vo.MessageVO;
import com.xiongyingqi.util.StringHelper;

/**
 * @author 瑛琪 <a href="http://xiongyingqi.com">xiongyingqi.com</a>
 * @version 2013-9-5 下午2:21:36
 */
public class EmotionDAOImpl implements EmotionDAO {
	/**
	 * 查询总数
	 */
	public static final String SQL_QUERY_EMOTION_COUNT = "select count(emotion_id) from kr_emotion ";
	/**
	 * 查询所有详情
	 */
	public static final String SQL_QUERY_ALL_EMOTIONS = "select emotion_id, emotion_uuid, emotion_string, emotion_file_path from kr_emotion order by emotion_id ";
	/**
	 * 查询概要消息
	 */
	public static final String SQL_QUERY_EMOTIONS_SUMMARY = "select emotion_id, emotion_uuid, emotion_string from kr_emotion order by emotion_id ";
	/**
	 * 根据EmotionId查询详情
	 */
	public static final String SQL_QUERY_EMOTION = "select emotion_id, emotion_uuid, emotion_string, emotion_file_path from kr_emotion where emotion_uuid = ? order by emotion_id ";
	/**
	 * 插入表情
	 */
	public static final String SQL_INSERT_EMOTION = "insert into kr_emotion(emotion_uuid, emotion_string, emotion_file_path) values (?, ?, ?) ";
	/**
	 * 根据Id查询表情详情
	 */
	public static final String SQL_QURY_EMOTION_DETAIL_BY_ID = "select emotion_id, emotion_uuid, emotion_string, emotion_file_path from kr_emotion where emotion_id = ? ";
	
	
	public static final Logger log = LoggerFactory
			.getLogger(EmotionDAOImpl.class);
	protected static Connection connection;
	
	public Connection getConnection(){
		try {
			connection = DbConnectionManager.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return connection;
//		return DBConnection.getConnection();
	}
	/**
	 * <br>2013-9-5 下午2:21:36
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#getAllEmotions()
	 */
	@Override
	public Collection<Emotion> getAllEmotions() {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<Emotion> emotions = new ArrayList<Emotion>();
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_QUERY_ALL_EMOTIONS);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int emotionId = rs.getInt(1);
				String emotionUUID = rs.getString(2);
				String emotionString = rs.getString(3);
				String emotionFilePath = rs.getString(4);
				
				Emotion emotion = new Emotion();
				emotion.setId(emotionId);
				emotion.setEmotionId(emotionUUID);
				emotion.setEmotionString(emotionString);
				emotion.setEmotionFile(new File(emotionFilePath));
				emotions.add(emotion);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return emotions;
	}

	/**
	 * <br>2013-9-5 下午2:21:36
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#getEmotion(java.lang.String)
	 */
	@Override
	public Emotion getEmotion(String emotionUUID) {
		Connection connection = null;
		PreparedStatement ps = null;
		Emotion emotion = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_QUERY_EMOTION);
			ps.setString(1, emotionUUID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				int emotionId = rs.getInt(1);
				String emotionString = rs.getString(3);
				String emotionFilePath = rs.getString(4);
				
				emotion = new Emotion();
				emotion.setId(emotionId);
				emotion.setEmotionString(emotionString);
				emotion.setEmotionFile(new File(emotionFilePath));
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return emotion;
	}
	
	

	/**
	 * <br>2013-9-5 下午2:21:36
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#addEmotion(com.kingray.openfire.plugin.vo.Emotion)
	 */
	@Override
	public Emotion addEmotion(Emotion emotion) {//SQL_INSERT_EMOTION
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_INSERT_EMOTION, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, emotion.getEmotionId());
			ps.setString(2, emotion.getEmotionString());
			ps.setString(3, emotion.getEmotionFile().getAbsolutePath());
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if(rs.next()){
				int emotionId = rs.getInt(1);
				emotion.setId(emotionId);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return emotion;
	}
	
	/**
	 * <br>2013-9-10 下午8:51:03
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#getEmotionSize()
	 */
	@Override
	public int getEmotionSize() {
		int size = 0;
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_QUERY_EMOTION_COUNT);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				size = rs.getInt(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return size;
	}
	/**
	 * <br>2013-9-10 下午9:33:08
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#getSummaryEmotions()
	 */
	@Override
	public Collection<Emotion> getSummaryEmotions() {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<Emotion> emotions = new ArrayList<Emotion>();
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_QUERY_EMOTIONS_SUMMARY);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int emotionId = rs.getInt(1);
				String emotionUUID = rs.getString(2);
				String emotionString = rs.getString(3);
				Emotion emotion = new Emotion();
				emotion.setId(emotionId);
				emotion.setEmotionId(emotionUUID);
				emotion.setEmotionString(emotionString);
				emotions.add(emotion);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return emotions;
	}
	/**
	 * <br>2013-9-10 下午9:41:13
	 * @see com.kingray.openfire.plugin.dao.EmotionDAO#getEmotionsByIds(java.util.Collection)
	 */
	@Override
	public Collection<Emotion> getEmotionsByIds(Collection<Emotion> emotions) {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<Emotion> emotionResults = new ArrayList<Emotion>();
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SQL_QURY_EMOTION_DETAIL_BY_ID);
			for (Iterator iterator = emotions.iterator(); iterator.hasNext();) {
				Emotion emotion = (Emotion) iterator.next();
				System.out.println("emotion.getId() ======== " + emotion.getId());
				ps.setInt(1, emotion.getId());
				
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					int emotionId = rs.getInt(1);
					String emotionUUID = rs.getString(2);
					String emotionString = rs.getString(3);
					String emotionFilePath = rs.getString(4);
					
					Emotion emotionResult = new Emotion();
					emotionResult.setId(emotionId);
					emotionResult.setEmotionString(emotionString);
					emotionResult.setEmotionId(emotionUUID);
					emotionResult.setEmotionFile(new File(emotionFilePath));
					emotionResults.add(emotionResult);
				}
			}
			
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return emotionResults;
	}

}
